package dao.search;

import bean.search.KeySearch;
import util.C3p0Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class KeySearchDao {
    public List<KeySearch> keySearch(String iKey){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<KeySearch> ks = null;

        String sql = "select i_key,s_number,s_name,s_sex,s_c_number,s_tel,r_number from t_incheck\n" +
                "right join t_student on t_incheck.i_number = t_student.s_r_number\n" +
                "left join t_room on t_room.r_number = t_incheck.i_number where i_key = ?;";

        try {
            conn = C3p0Util.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,iKey);
            rs = ps.executeQuery();
            while(rs.next()) {
                if (ks == null) {
                    ks = new ArrayList<KeySearch>();
                }
                KeySearch keySearch = new KeySearch();

                keySearch.setiKey(rs.getString("i_key"));
                keySearch.setsNumber(rs.getInt("s_number"));
                keySearch.setsName(rs.getString("s_name"));
                keySearch.setsSex(rs.getString("s_sex"));
                keySearch.setScNumber(rs.getInt("s_c_number"));
                keySearch.setsTel(rs.getInt("s_tel"));
                keySearch.setrNumber(rs.getInt("r_number"));

                ks.add(keySearch);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return ks;
    }
}
